package com.gy.common.excel;

import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

public class GyExcelUtil {
    // 打开excel也可以读取，写入必须关闭excel
    // 根据x,y写入

    public static void writerRL(Sheet sheet, int x, int y, String str) {
        Row row = sheet.getRow(x);
        // 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
        if (row == null) {
            row = sheet.createRow(x);
        }
        Cell cell = row.getCell(y);
        if (cell == null) {
            cell = row.createCell(y);
        }
        cell.setCellValue(str);
    }


    public static void writerRLString(Sheet sheet, int x, int y, String str, CellStyle style) {
        Row row = sheet.getRow(x);
        // 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
        if (row == null) {
            row = sheet.createRow(x);
        }
        Cell cell = row.getCell(y);
        if (cell == null) {
            cell = row.createCell(y);
        }
        cell.setCellValue(str);
        cell.setCellStyle(style);
    }

    public static void writerRLString(Sheet sheet, int x, int y, Date str, CellStyle style) {
        Row row = sheet.getRow(x);
        // 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
        if (row == null) {
            row = sheet.createRow(x);
        }
        Cell cell = row.getCell(y);
        if (cell == null) {
            cell = row.createCell(y);
        }
        cell.setCellValue(str);
        cell.setCellStyle(style);
    }


    public static void formula(HSSFSheet sheet, int x, int y, String str, CellStyle style) {
        HSSFRow row = sheet.getRow(x);

        // 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
        if (row == null) {
            row = sheet.createRow(x);
        }
        HSSFCell cell = row.getCell(y);
        if (cell == null) {
            cell = row.createCell(y);
        }
        cell.setCellStyle(style);
        // cell.setCellValue(str);
        cell.setCellFormula(str);
    }

    // 创建sheet
    public static HSSFSheet createSheet(HSSFWorkbook book, String str) {
        return book.createSheet(str);
    }

    /**
     * 黑色边框
     */
    public static CellStyle createBorderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        return style;
    }

    /**
     * 日期格式
     *
     * @param workbook
     * @return
     */
    public static CellStyle getStyle(Workbook workbook, String dateFormat) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        Font font = workbook.createFont();


        font.setFontName("宋体");  //字体
        font.setFontHeightInPoints((short) 11);  //设置字体大小
        cellStyle.setFont(font);//选择需要用到的字体格式

        CreationHelper createHelper = workbook.getCreationHelper();

        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(dateFormat));


        return cellStyle;
    }

    // 保存excel
    public static void saveExcel(HSSFWorkbook workbook, String path) {
        try (FileOutputStream fos = new FileOutputStream(path)) {
            workbook.write(fos);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }

    // 获取第几页的标题
    public static String readSheet(HSSFWorkbook workbook, int index) {
        HSSFSheet sheet = workbook.getSheetAt(index);
        return sheet.getSheetName();
    }

    // 遇到空就退出，返回个数，列是变化的
    public static int readRomXYEmpty(HSSFSheet sheet, List<String> str, int y) {
        String s = "";
        int i = 0;
        boolean b = true;
        do {
            HSSFRow row = sheet.getRow(i++);
            if (row == null) {
                break;
            }
            HSSFCell cell = row.getCell(y);

            s = getCellValue(cell);
            if ("".equals(s)) {
                break;
            }
            str.add(s);
        } while (b);
        return i - 1;
    }

    // 根据行列获取字符串
    public static String readXY(Sheet sheet, int x, int y) {
        // 列
        Row row = sheet.getRow(y);
        if (row == null) {
            return null;
        }
        // 行
        Cell cell = row.getCell(x);
        // 设置单元格的值,即C1的值(第一行,第三列)
        return getCellValue(cell);
    }

    // 根据行列获取字符串,空创建单元格
    public static String readXYCreate(HSSFSheet sheet, int x, int y) {
        // 列
        HSSFRow row = Optional.ofNullable(sheet.getRow(y)).orElseGet(() -> sheet.createRow(y));
        // 行
        HSSFCell cell = Optional.ofNullable(row.getCell(x)).orElseGet(() -> row.createCell(x));
        // 设置单元格的值,即C1的值(第一行,第三列)
        return getCellValue(cell);
    }


    /**
     * 获取sheet页第一行 多少列
     */

    public static short getLastCell(HSSFSheet sheet) {
        return sheet.getRow(sheet.getFirstRowNum()).getLastCellNum();
    }


    // 读取多行多列
    /*
     * rowSize 从第几行开始读 rowNum 列的个数 xBeg 从什么位置开始读取 xEnd 结束位置
     * 返回的list对象，string.length是一行的个数 list.size 是列的个数
     */
    public static List<String[]> ReadBegXEndX(HSSFSheet sheet, int colNum, int rowNum, int xBeg, int xEnd) {
        List<String[]> params = new ArrayList<>();
        for (int i = 0; i < rowNum; i++) {
            String[] param = new String[xEnd - xBeg];
            // 行
            HSSFRow row = sheet.getRow(i + colNum);
            // 列
            for (int j = 0; j < xEnd - xBeg; j++) {
                HSSFCell cell = row.getCell(j + xBeg);
                param[j] = getCellValue(cell);
            }
            params.add(param);
        }
        return params;
    }

    // 读取多行多列
    /*
     * colNum 从第几行开始读 rowNum 列的个数 xBeg 从什么位置开始读取 返回的list对象，string.length是一行的个数
     * list.size 是行的个数 string.length是列的个数
     */
    public static List<String[]> ReadBegX(HSSFSheet sheet, int colNum, int rowNum, int xBeg) {
        List<String[]> params = new ArrayList<>();
        for (int i = 0; i < rowNum; i++) {
            List<String> param = new ArrayList<>();
            // 行
            HSSFRow row = sheet.getRow(i + colNum);
            // 列
            int j = 0;
            do {
                HSSFCell cell = row.getCell(j + xBeg);
                if ("".equals(getCellValue(cell))) {
                    break;
                }
                param.add(getCellValue(cell));
                j++;
            } while (true);
            if (param.size() > 0) {
                String[] pa = new String[param.size()];
                for (j = 0; j < param.size(); j++) {
                    pa[j] = param.get(j);
                }
                params.add(pa);
            }
        }
        return params;
    }


    /**
     * 读取excel
     */
    public static List<Map<String, Object>> readByMap(HSSFSheet sheet, int colNum, int ignoreCol, int ignoreRow) {
        List<Map<String, Object>> params = Lists.newArrayList();
        HSSFRow row0 = sheet.getRow(0);
        int i = 0;
        do {
            // 行
            HSSFRow row = sheet.getRow(i + ignoreRow);
            if (row == null || Objects.equals(getCellValue(row.getCell(ignoreCol)), "")) {
                break;
            }
            Map<String, Object> param = new HashMap<>();
            // 列
            for (int j = 0; j < colNum; j++) {
                HSSFCell cell = row.getCell(j + ignoreCol);
                param.put(getCellValue(row0.getCell(j + ignoreCol)), getCellValue(cell));
            }
            params.add(param);
            i++;
        } while (true);
        return params;
    }


    public static List<String[]> ReadBeg(HSSFSheet sheet, int colNum, int xBeg) {
        List<String[]> params = new ArrayList<>();
        int i = 0;
        while (true) {

            List<String> param = new ArrayList<>();
            // 行
            HSSFRow row = sheet.getRow(i + colNum);
            if (null == row) {
                break;
            }
            i++;
            // 列
            int j = 0;
            do {
                HSSFCell cell = row.getCell(j + xBeg);
                if ("".equals(getCellValue(cell))) {
                    break;
                }
                param.add(getCellValue(cell));
                j++;
            } while (true);
            if (param.size() > 0) {

                String[] pa = new String[param.size()];
                for (j = 0; j < param.size(); j++) {
                    pa[j] = param.get(j);
                }
                params.add(pa);
            }
        }
        return params;
    }

    public static List<String[]> ReadBeg(Sheet sheet, int rowNum, int xBeg, int cl) {
        List<String[]> params = new ArrayList<>();
        int i = 0;
        while (true) {

            List<String> param = new ArrayList<>();
            // 行
            Row row = sheet.getRow(i + rowNum);
            if (null == row) {
                break;
            }
            i++;
            // 列
            int j = 0;
            for (int ii = 0; ii < cl; ii++) {
                Cell cell = row.getCell(j + xBeg);
                param.add(getCellValue(cell));
                j++;
            }
            if (param.size() > 0) {

                String[] pa = new String[param.size()];
                for (j = 0; j < param.size(); j++) {
                    pa[j] = param.get(j);
                }
                params.add(pa);
            }
        }
        return params;
    }

    public static String getCellValue(Cell cell) {
        String ret = "";
        if (null == cell) {
            return "";
        }
        switch (cell.getCellTypeEnum()) {
            case _NONE:
                ret = "";
                break;
            case BLANK:
                ret = "";
                break;
            case BOOLEAN:
                // 返回布尔类型的值
                ret = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                ret = null;
                break;
            case FORMULA:
                // 公式
                Workbook wb = cell.getSheet().getWorkbook();
                CreationHelper crateHelper = wb.getCreationHelper();
                FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
                ret = getCellValue(evaluator.evaluateInCell(cell));
                break;
            case NUMERIC:
                // 返回数值类型的值，日期类型的数据在EXCEL表中也是数值型
                if (HSSFDateUtil.isCellDateFormatted(cell)) { // 判断是日期类型
                    SimpleDateFormat dateformat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    Date dt = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());// 获取成DATE类型
                    ret = dateformat.format(dt);
                } else if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                    SimpleDateFormat dateformat = new SimpleDateFormat("h:mm");
                    Date dt = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());// 获取成DATE类型
                    ret = dateformat.format(dt);
                } else if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd")) {
                    SimpleDateFormat dateformat = new SimpleDateFormat("yyyy/MM/dd");
                    Date dt = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());// 获取成DATE类型
                    ret = dateformat.format(dt);
                } else if (cell.getCellStyle().getDataFormat() == 57) {
                    SimpleDateFormat dateformat = new SimpleDateFormat("yyyy/M/d");
                    Date dt = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());// 获取成DATE类型
                    ret = dateformat.format(dt);
                } else {
                    short is = cell.getCellStyle().getDataFormat();
                    // DecimalFormat df = new DecimalFormat("0");
                    // ret = df.format(cell.getNumericCellValue());
                    ret = NumberToTextConverter.toText(cell.getNumericCellValue());
                }
                break;
            case STRING:
                // 返回字符串类型的值
                ret = cell.getRichStringCellValue().getString();
                // ret=cell.toString();
                break;
            default:
        }
        return ret;
    }

    // 读取一列，并判断是否有符合的值，有返回列，设置最大值防止没有选择进入死循环
    public static int readSelectRowStr(HSSFSheet sheet, String Em, int x, int max) {
        int y = 0;

        HSSFCell cell = null;
        do {
            // 防止死循环
            if (max < y) {
                break;
            }
            // 列
            HSSFRow row = sheet.getRow(y++);
            // 行
            cell = row.getCell(x);
        } while (!getCellValue(cell).equals(Em));
        return y - 1;
    }

    public static void main(String[] args) throws IOException {

        // 创建输入流
        FileInputStream fis = new FileInputStream(new File("I:/一键生成/cover/Cover0001/xls/Cover0001.xls"));
        // 通过构造函数传参
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        // 获取工作表
        HSSFSheet sheet = workbook.getSheetAt(0);

        workbook.close();
        fis.close();

    }


    /**
     * 获取A-列号
     *
     * @param i
     * @return
     */
    public static String getColumnLetter(int i) {
        return CellReference.convertNumToColString(i);
    }


}
